#!/usr/bin/python
# -*- coding: utf-8 -*-
"""
Mikan 開発用 パッケージ

Mikan Python Web Framework
"""

__author__ = "K.Sonohara"
__status__ = "develop"
__version__ = "0.0.0_36"
__date__    = "2014/01/05"
__license__ = 'LGPL'

# ------------------------------------------------------------
## インポート
# ------------------------------------------------------------
# Python
import traceback
import os
import datetime

# SQLAlchemy
from sqlalchemy.schema import MetaData, Table
from sqlalchemy import Column, String, Integer, ForeignKey, create_engine, Date, DateTime, BigInteger, Float, SmallInteger, BOOLEAN, text
from sqlalchemy.orm import mapper, sessionmaker
from sqlalchemy import and_
import sqlalchemy

# Mikan
from mikan.orm.sqlalchemy.session import MikanSession
from mikan.item import MIKAN_ITEM
from mikan.exception import MikanExceptions
from mikan.orm import AbstractMikanORM
from mikan.db import MikanNullObject

# ESiPC
from esipc.util import load_config
from esipc import ESiPCProperty

# ------------------------------------------------------------
## 変数
# ------------------------------------------------------------

# ------------------------------------------------------------
## 関数
# ------------------------------------------------------------
def convertAttribute2Column(attribute):
	if attribute.isdynamic:
		return None

	t = None
	if attribute.classtype == MIKAN_ITEM.CLASS_TYPE_LONG:
		t = BigInteger
	elif attribute.classtype == MIKAN_ITEM.CLASS_TYPE_INT:
		t = Integer
	elif attribute.classtype == MIKAN_ITEM.CLASS_TYPE_SHORT:
		t = SmallInteger
	elif attribute.classtype == MIKAN_ITEM.CLASS_TYPE_FLOAT:
		t = Float
	elif attribute.classtype == MIKAN_ITEM.CLASS_TYPE_BOOL:
		t = SmallInteger
	elif attribute.classtype == MIKAN_ITEM.CLASS_TYPE_TIMESTAMP:
		t = DateTime
	elif attribute.classtype == MIKAN_ITEM.CLASS_TYPE_TIME:
		t = DateTime
	elif attribute.classtype == MIKAN_ITEM.CLASS_TYPE_DATE:
		t = Date
	else:
		t = String(attribute.length)

	null = attribute.isrequired

	c = Column(attribute.definename, t, nullable=not null)

	if c.name == "id":
		c.primary_key = True
#		c.server_default = ObjectId().__str__()
#	elif c.name == "createdate":
#		c.server_default = text('NOW()')
#	elif c.name == "lastupdate":
#		c.server_default = text('NOW()')
#		c.server_onupdate = text('NOW()')

#	if attribute.list:
#		if attribute.list.isentitylist:
#	#		print "@" + attribute.list.definename
#			fk = ForeignKey("%s.%s" % (attribute.list.definename, "id"))
#	#		print "@" + fk.__str__()
#			c.append_foreign_key(fk)

	return c

def convert_entity_table(metadata, entity):
	t = Table(entity.definename, metadata)

	for v in entity.sorted_attributes():
#		print unicode(v)
		c = convertAttribute2Column(v)
		if c is not None:
			t.append_column(c)

	return t

# ------------------------------------------------------------
## クラス
# ------------------------------------------------------------
class AbstractMikanSQLAlchemyORM(AbstractMikanORM):

	def get_mappers(self):
		return self.FMappers

	def is_mappered(self, entityclass):
		if entityclass in self.mappers:
			return True
		return False

	def __init__(self,  request):
		super(AbstractMikanSQLAlchemyORM, self).__init__(request)

		self.FMappers = []

	def setup_orm(self, ini):
		for s in ini.sections():
			for o in ini.options(s):
				try:
					name = ini.get(s, o)
					# モジュールインポート
					module = __import__(s, fromlist=[s])
					clazz = getattr(module, name)

					self.mapper_entity(clazz())
				except:
					if self.islogdebug:
						self.logger.debug(traceback.format_exc())
					pass


	def setup(self, config, section):
		url = "sqlite:///:memory:"
		try:
			url = config.get(section, "url")
		except:
			pass

		sql = self.islogdebug
#		sql = True
		self.FEngine = create_engine(url, echo=sql)
		self.FMetadata = MetaData(self.engine)
		if self.islogdebug:
			self.logger.debug(self.FEngine)
			self.logger.debug(self.FMetadata)


		s = sessionmaker(autoflush=False)
		s.configure(bind=self.engine)
		self.FSession = s()

		# mapper
		if self.request:
			try:
				bp = os.path.join(self.request.mikanroot, "etc")
				bp = os.path.join(bp, "orm.ini")

				ini = load_config(bp)
				self.setup_orm(ini)
			except:
				self.logger.error(traceback.format_exc())


	def get_engine(self):
		return self.FEngine

	def get_session(self):
		return self.FSession

	def get_metadata(self):
		return self.FMetadata

	def convert_table(self, entity):
		return convert_entity_table(self.metadata, entity)

	def mapper_entity(self, entity):
		if not self.is_mappered(entity.__class__):
			t = self.convert_table(entity)
			try:
				mapper(entity.__class__, t)
			except:
				pass
			self.mappers.append(entity.__class__)

	def commit(self):
		self.session.flush()
		self.session.commit()

	def rollback(self):
		self.session.rollback()

	def close(self):
		if (self.session != None):
			try:
				self.session.rollback()
			except:
				pass
			try:
				self.session.close()
			except:
				pass
		self.FSession   = None

	def get_datetime_now(self):
		if self.request and self.request.timezone:
			return datetime.datetime.now(self.request.timezone)
		else:
			return datetime.datetime.now()

	def create(self, entity, sequence=False, newid=True):
		if newid:
			try:
				entity.onCreate()

				if entity.is_date_entity():
					entity.createdate = self.get_datetime_now()
					entity.lastupdate = self.get_datetime_now()
			except:
#				print traceback.format_exc()
				if self.islogdebug:
					self.logger.debug(traceback.format_exc())

			if entity.issequenceentity:
				if sequence:
					entity.setSequenceValue(self.get_sequence_value_next(entity, entity.getSequenceValue()))
					entity.setRootIDValue(entity.getIDValue())
					entity.setEffectiveSequenceValue(True)
					entity.setLastSequenceValue(True)
				else:
					master = entity.getMasterClass()({})
					master = self.create(master)

					entity.setSequenceValue(self.get_sequence_value_first(entity))
					entity.setRootIDValue(entity.getIDValue())
					entity.setMasterIDValue(master.getIDValue())
					entity.setEffectiveSequenceValue(True)
					entity.setLastSequenceValue(True)

		ex = self.validate(entity)
		if ex:
			e = MikanExceptions(self.request)
			e.exceptions.extend(ex)
			raise e

		a = entity.__class__(entity.export_values())
		for k,v  in entity.attributes.items():
			if a.values.has_key(k):
				if a.values[k] != None:
					if v.classtype == "time" or v.classtype == "timestamp":
						a.values[k] = self.convert_to_utc_datetime_object(a.values[k])
					elif v.classtype == "bool":
						a.values[k] = self._cast_bool_to(a.values[k])
						entity.values[k] = self._cast_bool_from(entity.values[k])

#		if self.isdebuglog:
#			self.logger.debug(a)

		self.session.add(a)

		return entity

	def _cast_bool_to(self, value):
		if value == 1:
			return 1
		if value == True:
			return 1
		try:
			if value.lower() == "true":
				return 1
		except:
			pass
		return 0

	def _cast_bool_from(self, value):
		if value == 1:
			return True
		if value == True:
			return True
		try:
			if value.lower() == "true":
				return True
		except:
			pass
		return False

	def update(self, entity):
		try:
			entity.onUpdate()

			if entity.is_date_entity():
				entity.lastupdate = self.get_datetime_now()
		except:
			if self.islogdebug:
				self.logger.debug(traceback.format_exc())

		if self.islogdebug:
			self.logger.debug(entity)
		ex = self.validate(entity)
		if ex:
			e = MikanExceptions(self.request)
			e.exceptions.extend(ex)
			raise e

		e = self.get_entity(entity.__class__, entity.id)

		for k, v in entity.attributes.items():
			if entity.values.has_key(k):
				_v = entity.values[k]

				if e.values.has_key(k) and e.values[k] != None:
					if v.classtype == "time" or v.classtype == "timestamp":
						_v = self.convert_to_utc_datetime_object(entity.values[k])
				if v.classtype == "bool":
					_v = self._cast_bool_to(_v)

				setattr(e, k, _v)

#		if self.isdebuglog:
#			self.logger.debug(e)

		self.session.flush()

		return entity

	def remove(self, entity):
		try:
			entity.onRemove()
		except:
			if self.islogdebug:
				self.logger.debug(traceback.format_exc())

		e = self.get_entity(entity.__class__, entity.id)
		self.session.delete(e)

		return entity

	def creates(self, items):
		if self.islogdebug:
			self.logger.debug(items)
		for e in list:
			self.create(e)

	def removes(self, items):
		if self.islogdebug:
			self.logger.debug(items)
		for e in items:
			self.remove(e)

	def updates(self, items):
		if self.islogdebug:
			self.logger.debug(items)
		for e in items:
			self.update(e)

	def get(self, entityclass, eid):
		e = self.get_entity(entityclass, eid)
		if e:
			return entityclass(e.export_values())

		return None

	def get_entity(self, entityclass, eid):
		query = self.session.query(entityclass)

		query = query.filter((entityclass.id == eid))
		row = query.first()
		if row:
			row.init_attibutes()

			for k, v in row.attributes.items():
				if row.values.has_key(k):
					if row.values[k] != None:
						if v.classtype == "time" or v.classtype == "timestamp":
							row.values[k] = self.convert_from_utc_datetime_object(row.values[k])
						elif v.classtype == "bool":
							row.values[k] = self._cast_bool_from(row.values[k])

			return row

		return None

	def _order_by(self, query, o, desc):
		if desc:
			return query.order_by(o.desc())
		else:
			return query.order_by(o)

	def _gets(self, entityclass, wheres=None, orderby=None, lockmode=None, offset=None, limit=None):
		q = self.session.query(entityclass)

		# where
		if wheres is not None:
			if isinstance(wheres, list) or isinstance(wheres, tuple):
				for w in wheres:
					q = q.filter(w)
			else:
				q = q.filter(wheres)

		# lock
		if lockmode:
			q = q.with_lockmode(lockmode)
#		'update'
#		'update_nowait'
#		'read'

		# order by
		if orderby is not None:
			if isinstance(orderby, list):
				for ord, d in orderby:
					q = self._order_by(q, ord, d)
			else:
				ord, d = orderby
				q = self._order_by(q, ord, d)

		# offset
		if offset is not None:
			q = q.offset(offset)
		if limit is not None:
			q = q.limit(limit)

		return q

	def count(self, entityclass, wheres=None, orderby=None, offset=None, limit=None):
		q = self._gets(entityclass, wheres, orderby, lockmode=None, offset=offset, limit=limit)

		# execute
		return q.count()

	def _get_row(self, entityclass, l, row):
		row.init_attibutes()

		for k, v in row.attributes.items():
			if row.values.has_key(k):
				if row.values[k] != None:
					if v.classtype == "time" or v.classtype == "timestamp":
						row.values[k] = self.convert_from_utc_datetime_object(row.values[k])
					elif v.classtype == "bool":
						row.values[k] = self._cast_bool_from(row.values[k])

		l.append(entityclass(row.export_values()))

	def gets(self, entityclass, wheres=None, orderby=None, lockmode=None, offset=None, limit=None):
		l = []
		q = self._gets(entityclass, wheres, orderby, lockmode=lockmode, offset=offset, limit=limit)
		qa = q.all()

		for row in qa:
			self._get_row(entityclass, l, row)

		return l

	def copy(self, entity, sequence=None):
		e = entity.__class__(entity.export_values())

		return self.create(e, sequence)

	def query_count(self, entityclass, entity, like=True):
		wheres, orderby = self._query(entityclass, entity, like)

		return self.count(entityclass, wheres=wheres, orderby=orderby, lockmode=None)

	def _query(self, entityclass, entity, like=True):
		# where
		wheres = self.where(entityclass, entity, entity.export_values(dynamic=False), like=like)
		orderby = self._orderby(entityclass, entity)

		# log
		if self.islogdebug:
			self.logger.debug(wheres)

		return (wheres, orderby)

	def _orderby(self, entityclass, entity):
		#order by
		orderby=None

		if entity.sortattributes:
			orderby = []
			for o, d in entity.sortattributes:
				a = getattr(entityclass, o.definename)
				orderby.append((a, d))

		# log
		if self.islogdebug:
			self.logger.debug(orderby)

		return orderby

	def query(self, entityclass, entity, like=True, lockmode=None):
		wheres, orderby = self._query(entityclass, entity, like)

		offset = None
		try:
			offset = entity.offsetof
		except:
			if self.islogdebug:
				self.logger.debug(traceback.format_exc())
		limit = None
		try:
			limit = entity.numberof
		except:
			if self.islogdebug:
				self.logger.debug(traceback.format_exc())

		return self.gets(entityclass, wheres=wheres, orderby=orderby, lockmode=lockmode, offset=offset, limit=limit)

	def where(self, entityclass, entity, wheres, like=True):
		w = []

		q = {}
		q.update(wheres)
		for k, v in q.items():
			if (v != None) and (v != ""):
				at = entity.get_attribute(k)
				if at.isdynamic:
					break
				a = getattr(entityclass, k)
				# log
				if self.islogdebug:
					self.logger.debug(at)
					self.logger.debug(a)
					self.logger.debug(v)

				if isinstance(v, MikanNullObject):
					w.append(a == None)
				elif at.list:
					w.append(a == v)
				else:
					if like:
						if isinstance(v, str):
							w.append(a.like("%" + v + "%"))
						elif isinstance(v, unicode):
							w.append(a.like("%" + v + "%"))
						else:
							w.append(a == v)
					else:
						w.append(a == v)

		# log
		if self.islogdebug:
			self.logger.debug(w)

		return w

	def get_orm_version(self):
		return sqlalchemy.__version__

	def get_orm_name(self):
		return self.engine.__class__

	## Property
	engine = ESiPCProperty(get_engine, None, None)
	session = ESiPCProperty(get_session, None, None)
	metadata = ESiPCProperty(get_metadata, None, None)
	mappers = ESiPCProperty(get_mappers, None, None)

# ------------------------------------------------------------
## メイン関数
# ------------------------------------------------------------
if __name__ == '__main__':
	pass

# ------------------------------------------------------------
